﻿Imports System.Data.SqlServerCe

Public Class Setup
    Public Shared Function IsUpToDate() As Boolean
        If Not IsLocalDatabaseSetup Then Return False
        If Not IsLocalDatabaseUpToDate Then Return False
        Return True
    End Function

    Public Shared ReadOnly Property IsLocalDatabaseSetup() As Boolean
        Get
            If String.IsNullOrEmpty(Environment.DBPath) Then Return False
            If Not IO.File.Exists(Environment.DBPath) Then Return False
            If String.IsNullOrEmpty(Environment.DBPassword) Then Return False
            Return True
        End Get
    End Property
    Public Shared ReadOnly Property IsLocalDatabaseUpToDate() As Boolean
        Get
            Return MetaData.GetDBVersion(DataSource.Local) = MetaData.GetDBVersion(DataSource.Template)
        End Get
    End Property

    Public Shared Sub SyncronizeDatabases()
        CreateBackup()

        Using t As New DataStore.TransactionScope
            Dim strSql As String = "select table_name from information_schema.tables"

            Dim aExistingTables As Generic.List(Of String)
            aExistingTables = DataStore.Environment.ExecuteCommandToStringList(strSql)

            Dim aNeededTables As Generic.List(Of String)
            aNeededTables = DataStore.Environment.ExecuteCommandToStringList(strSql, DataSource.Template)

            CreateMissingTables(t, aNeededTables, aExistingTables)

            AlterExistingTables(t, aNeededTables, aExistingTables)

            DropObsoleteTables(t, aNeededTables, aExistingTables)

            SetCorrectVersion(t)

            t.Complete()
        End Using
    End Sub

    Private Shared Sub SetCorrectVersion(ByVal transactionScope As TransactionScope)
        Dim strVersion As String = MetaData.GetDBVersion(DataSource.Template)
        MetaData.SetDBVersion(strVersion, transactionScope)
    End Sub

    Private Shared Sub CreateBackup()
        Dim strVersion As String = MetaData.GetDBVersion(DataSource.Local)
        Dim strFileName As String = IO.Path.GetFileNameWithoutExtension(Environment.DBPath)
        Dim strFolder As String = IO.Path.GetDirectoryName(Environment.DBPath)
        Dim strBackupFileName As String = IO.Path.Combine(strFolder, strFileName & "_v" & strVersion & ".sdf")
        If IO.File.Exists(strBackupFileName) Then IO.File.Delete(strBackupFileName)

        IO.File.Copy(Environment.DBPath, strBackupFileName)
    End Sub


    Private Shared Sub AlterExistingTables(ByVal transactionScope As TransactionScope, ByVal neededTables As Generic.List(Of String), ByVal existingTables As Generic.List(Of String))
        For Each strExistingTableName As String In existingTables
            Dim blnAlter As Boolean = False
            For Each strNeededTableName As String In neededTables
                If strExistingTableName.ToLower = strNeededTableName.ToLower Then
                    blnAlter = True
                    Exit For
                End If
            Next
            If blnAlter Then
                Dim aExistingCols As Generic.List(Of Environment.ColumnInfo)
                aExistingCols = Environment.GetColumnInfos(strExistingTableName, transactionScope)
                Dim aNeededCols As Generic.List(Of Environment.ColumnInfo)
                aNeededCols = Environment.GetColumnInfos_Template(strExistingTableName)

                For Each clsExistingCol As Environment.ColumnInfo In aExistingCols
                    Dim clsNeededCol As Environment.ColumnInfo = Nothing
                    For Each clsNeededColTmp As Environment.ColumnInfo In aNeededCols
                        If clsExistingCol.Name.ToLower = clsNeededColTmp.Name.ToLower Then
                            clsNeededCol = clsNeededColTmp
                            Exit For
                        End If
                    Next

                    If clsNeededCol IsNot Nothing Then
                        'make sure type and size is correct
                        Dim blnEqual As Boolean = True
                        If clsNeededCol.DataType.ToLower <> clsExistingCol.DataType.ToLower Then blnEqual = False
                        If clsNeededCol.CharMaxLen <> clsExistingCol.CharMaxLen Then blnEqual = False
                        If clsNeededCol.IsNullable <> clsExistingCol.IsNullable Then blnEqual = False
                        If Not blnEqual Then
                            Dim strColDef As String = GetColDef(clsNeededCol)
                            Dim strAlterSql As String = String.Format("ALTER TABLE {0} ALTER {1}", strExistingTableName, strColDef)
                            Environment.ExecuteNonQuery(strAlterSql, transactionScope)
                        End If
                    Else
                        Dim strDropSql As String = String.Format("ALTER TABLE {0} DROP COLUMN {1}", strExistingTableName, clsExistingCol.Name)
                        Environment.ExecuteNonQuery(strDropSql, transactionScope)
                    End If

                Next

                For Each clsNeededColTmp As Environment.ColumnInfo In aNeededCols
                    Dim blnAddCol As Boolean = True
                    For Each clsExistingCol As Environment.ColumnInfo In aExistingCols
                        If clsExistingCol.Name.ToLower = clsNeededColTmp.Name.ToLower Then
                            blnAddCol = False
                            Exit For
                        End If
                    Next
                    If blnAddCol Then
                        Dim strColDef As String = GetColDef(clsNeededColTmp)
                        Dim strAddSql As String = String.Format("ALTER TABLE {0} ADD {1}", strExistingTableName, strColDef)
                        Environment.ExecuteNonQuery(strAddSql, transactionScope)
                    End If
                Next
            End If
        Next
    End Sub
    Private Shared Function GetColDef(ByVal col As Environment.ColumnInfo) As String
        Dim clsSb As New System.Text.StringBuilder("COLUMN ")
        clsSb.Append(col.Name)
        clsSb.Append(" ")
        clsSb.Append(col.DataType)
        clsSb.Append(" ")
        If col.CharMaxLen > 0 AndAlso col.DataType.ToLower <> "ntext" Then
            clsSb.Append("("c)
            clsSb.Append(col.CharMaxLen)
            clsSb.Append(")"c)
        End If
        If col.IsNullable Then
            clsSb.Append(" NULL")
        Else
            clsSb.Append(" NOT NULL")
        End If
        Return clsSb.ToString
    End Function

    Private Shared Sub DropObsoleteTables(ByVal transactionScope As TransactionScope, ByVal neededTables As Generic.List(Of String), ByVal existingTables As Generic.List(Of String))
        For Each strExistingTableName As String In existingTables
            Dim blnRemove As Boolean = True
            For Each strTableName As String In neededTables
                If strTableName.ToLower = strExistingTableName.ToLower Then
                    blnRemove = False
                    Exit For
                End If
            Next
            If blnRemove Then
                Dim strDropSql As String = String.Format("DROP TABLE {0}", strExistingTableName)
                Environment.ExecuteNonQuery(strDropSql, transactionScope)
            End If
        Next
    End Sub


    Private Shared Sub CreateMissingTables(ByVal transactionScope As TransactionScope, ByVal neededTables As Generic.List(Of String), ByVal existingTables As Generic.List(Of String))
        For Each strTableName As String In neededTables
            Dim blnCreate As Boolean = True
            For Each strExistingTableName As String In existingTables
                If strTableName.ToLower = strExistingTableName.ToLower Then
                    blnCreate = False
                    Exit For
                End If
            Next
            If blnCreate Then
                Dim strCreateTableSql As String = GetCreateTableStatement(strTableName)
                DataStore.Environment.ExecuteNonQuery(strCreateTableSql, transactionScope)

                Dim aIndexes() As DataStore.Environment.IndexInfo
                aIndexes = DataStore.Environment.GetIndexInfos_Template(strTableName)
                For Each clsIdx As DataStore.Environment.IndexInfo In aIndexes
                    If Not clsIdx.IsPrimaryKey Then
                        Dim clsSql As New System.Text.StringBuilder("CREATE")
                        If clsIdx.IsUnique Then clsSql.Append(" UNIQUE")
                        clsSql.Append(" INDEX ")
                        clsSql.Append(clsIdx.Name)
                        clsSql.Append(" ON ")
                        clsSql.Append(strTableName)
                        clsSql.Append(" (")
                        Dim blnFirst As Boolean = True
                        For Each strColName As String In clsIdx.ColNames
                            If blnFirst Then
                                blnFirst = False
                            Else
                                clsSql.Append(","c)
                            End If
                            clsSql.Append(strColName)
                        Next
                        clsSql.Append(")"c)

                        DataStore.Environment.ExecuteNonQuery(clsSql.ToString, transactionScope)
                    End If
                Next
            End If
        Next


    End Sub

    Private Shared Function GetCreateTableStatement(ByVal tableName As String) As String
        Dim clsSb As New System.Text.StringBuilder

        Dim aCols As Generic.List(Of DataStore.Environment.ColumnInfo)
        aCols = DataStore.Environment.GetColumnInfos_Template(tableName)

        Dim aIndexes() As DataStore.Environment.IndexInfo
        aIndexes = DataStore.Environment.GetIndexInfos_Template(tableName)

        clsSb.Append("CREATE TABLE ")
        clsSb.Append(tableName)
        clsSb.Append(" (")

        Dim blnAddComma As Boolean = False
        For Each clsCol As DataStore.Environment.ColumnInfo In aCols
            If blnAddComma Then
                clsSb.Append(", ")
            Else
                blnAddComma = True
            End If

            clsSb.Append("["c)
            clsSb.Append(clsCol.Name)
            clsSb.Append("] ")
            clsSb.Append(clsCol.DataType)
            If clsCol.CharMaxLen > 0 And clsCol.DataType.ToLower <> "ntext" Then
                clsSb.Append(" (")
                clsSb.Append(clsCol.CharMaxLen)
                clsSb.Append(")"c)
            End If
            If Not clsCol.IsNullable Then clsSb.Append(" NOT NULL")
            If clsCol.AutoInc_Increment > 0 Then
                clsSb.Append(" IDENTITY(")
                clsSb.Append(clsCol.AutoInc_Increment)
                clsSb.Append(","c)
                clsSb.Append(clsCol.AutoInc_Seed)
                clsSb.Append(")"c)
            End If
        Next

        For Each clsIdx As DataStore.Environment.IndexInfo In aIndexes
            If clsIdx.IsPrimaryKey Then
                If blnAddComma Then
                    clsSb.Append(", ")
                Else
                    blnAddComma = True
                End If
                clsSb.Append(" PRIMARY KEY (")
                Dim blnFirst As Boolean = True
                For Each strColName As String In clsIdx.ColNames
                    If Not blnFirst Then
                        clsSb.Append(","c)
                    Else
                        blnFirst = False
                    End If
                    clsSb.Append("["c)
                    clsSb.Append(strColName)
                    clsSb.Append("]"c)
                Next
                clsSb.Append(")"c)
            End If
        Next

        clsSb.Append(")"c)
        Return clsSb.ToString
    End Function

End Class
